﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Lookstone.Data;
using Lookstone.Data.SQLite;

namespace Weber.CS3280.Inventory.Data
{
	public class InventoryItem : SQLiteInt32Key
	{
		private Model.InventoryItem _item;

		public InventoryItem()
			: base()
		{
			_item = new Model.InventoryItem()
				{
					Name = string.Empty,
				};
		}

		public InventoryItem(Model.InventoryItem source)
			: base()
		{
			_item = new Model.InventoryItem()
				{
					InventoryItemId = source.InventoryItemId,
					ItemCategoryId = source.ItemCategoryId,
					ItemCode = source.ItemCode,
					Name = source.Name,
					RetailPrice = source.RetailPrice,
					Cost = source.Cost,
				};

			InternalID = _item.InventoryItemId ?? 0;
			IsNewRecord = InternalID == 0;
		}

		public InventoryItem(InventoryConnectionContext context, int id)
			: base(context, id)
		{
		}

		public InventoryItem(RowReader reader)
			: base(reader)
		{
		}

		protected override void LoadRowData(Lookstone.Data.RowReader reader)
		{
			_item = new Model.InventoryItem();
			InternalID = reader.GetField<int>("InventoryItemId");
			_item.InventoryItemId = InternalID;
			_item.Name = reader.GetField<string>("Name");
			_item.ItemCategoryId = reader.GetField<int>("ItemCategoryId");
			_item.ItemCode = reader.GetField<int>("ItemCode");
			_item.RetailPrice = Lookstone.Convert.Currency.CentsToDecimal(reader.GetField<long>("RetailPriceCents"));
			_item.Cost = Lookstone.Convert.Currency.CentsToDecimal(reader.GetField<long>("CostCents"));
		}

		protected override SQLiteCommand GetSaveCommand()
		{
			SQLiteCommand cmd = new SQLiteCommand() { CommandType = CommandType.Text };

			if (IsNewRecord)
				cmd.CommandText = GetInsertQuery();
			else
			{
				cmd.CommandText = GetUpdateQuery();
				cmd.Parameters.Add("@InventoryItemId", DbType.Int32).Value = ID;
			}

			cmd.Parameters.Add("@ItemCategoryId", DbType.Int32).Value = _item.ItemCategoryId;
			cmd.Parameters.Add("@ItemCode", DbType.Int32).Value = _item.ItemCode;
			cmd.Parameters.Add("@Name", DbType.String).Value = _item.Name;
			cmd.Parameters.Add("@RetailPriceCents", DbType.Int32).Value = Lookstone.Convert.Currency.DecimalToCentsInt32(_item.RetailPrice);
			cmd.Parameters.Add("@CostCents", DbType.Int32).Value = Lookstone.Convert.Currency.DecimalToCentsInt32(_item.Cost);
			return cmd;
		}

		public string Name
		{
			get { return _item.Name; }
			set { _item.Name = value; }
		}

		public int ItemCategoryId
		{
			get { return _item.ItemCategoryId; }
			set { _item.ItemCategoryId = value; }
		}

		public int ItemCode
		{
			get { return _item.ItemCode; }
			set { _item.ItemCode = value; }
		}

		public decimal RetailPrice
		{
			get { return _item.RetailPrice; }
			set { _item.RetailPrice = value; }
		}

		public decimal Cost
		{
			get { return _item.Cost; }
			set { _item.Cost = value; }
		}

		#region //****** Queries ******//
		private static string GetInsertQuery()
		{
			return
				"INSERT INTO " +
					"InventoryItem " +
				"( " +
					" ItemCategoryId " +
					",ItemCode " +
					",Name " +
					",RetailPriceCents " +
					",CostCents " +
				") " +
				"VALUES " +
				"( " +
					" @ItemCategoryId " +
					",@ItemCode " +
					",@Name " +
					",@RetailPriceCents " +
					",@CostCents " +
				"); " +
				"SELECT last_insert_rowid() AS InventoryItemId ";
		}

		private static string GetSelectAllQuery()
		{
			return
				"SELECT " +
					 "InventoryItemId " +
					",ItemCategoryId " +
					",ItemCode " +
					",Name " +
					",RetailPriceCents " +
					",CostCents " +
				"FROM " +
					"InventoryItem " +
				"ORDER BY " +
					"Name Asc ";
		}

		private static string GetSelectByNameQuery()
		{
			return
				"SELECT " +
					 "InventoryItemId " +
					",ItemCategoryId " +
					",ItemCode " +
					",Name " +
					",RetailPriceCents " +
					",CostCents " +
				"FROM " +
					"InventoryItem " +
				"WHERE " +
					"Name LIKE @NameFilter " +
				"ORDER BY " +
					"Name Asc ";
		}

		private static string GetUpdateQuery()
		{
			return
				"UPDATE " +
					"InventoryItem " +
				"SET " +
					",ItemCategoryId = @ItemCategoryId " +
					",ItemCode = @ItemCode " +
					",Name = @Name " +
					",RetailPriceCents = @RetailPriceCents " +
					",CostCents = @CostCents " +
				"WHERE " +
					"InventoryItemId = @InventoryItemId ";
		}
		#endregion //****** Queries ******//

		public Model.InventoryItem ToModel()
		{
			return _item;
		}

		public static Model.InventoryItem[] GetInventoryItemsByName(InventoryConnectionContext context, string name)
		{
			using (SQLiteCommand cmd = new SQLiteCommand(GetSelectByNameQuery()) { CommandType = CommandType.Text })
			{
				context.ConnectCommand(cmd);
				cmd.Parameters.AddWithValue("@NameFilter", string.Format("%{0}%", name));
				return LoadBulk<InventoryItem>(cmd)
					.Select(c => c._item)
					.ToArray();
			}
		}

		public static Model.InventoryItem[] GetAllInventoryItems(InventoryConnectionContext context)
		{
			using (SQLiteCommand cmd = new SQLiteCommand(GetSelectAllQuery()) { CommandType = CommandType.Text })
			{
				context.ConnectCommand(cmd);
				return LoadBulk<InventoryItem>(cmd)
					.Select(c => c.ToModel())
					.ToArray();
			}
		}
	}
}
